# coding: utf-8

# NOTE by zhouhl 2018-02-05
# Step1. 查询拥有多个套餐记录的user，无须理会 pack.deleted_at 是否等于0
# Step2. 遍历这些用户的套餐记录
# Step2-1. 在上述记录找到deleted_at=0且id最大的记录。如果不存在则转 Step2-3
# Step2-2. 统计该用户所有套餐记录中，各个字段的最大值，统一更新到 id 最大的记录
# Step2-3. 删除该用户其他套餐记录
# Step3. 遍历完成后，pack.uid 增加唯一索引

import os

import set_env_path     # 请勿删除

from sqlalchemy import create_engine
from sqlalchemy.sql import text


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def clear_duplicate_pack():
    find_duplicate_pack_users_sql = text("""
        select uid from pack group by uid HAVING count(uid)>1;
    """)

    find_pack_with_uid_sql = text("""
        select * from pack where uid=:uid order by id asc;
    """)

    update_valid_pack_sql = text("""
        update
            pack
        set
            call_point = :max_call_point,
            bid_point = :max_bid_point,
            sms_point = :max_sms_point,
            dispatch_point = :max_dispatch_point,
            paid_sms_point = :max_paid_sms_point,
            accum_call_point = :max_accum_call_point,
            accum_bid_point = :max_accum_bid_point,
            accum_sms_point = :max_accum_sms_point,
            accum_dispatch_point = :max_accum_dispatch_point,
            end_date = :max_end_date,
            sms_end_date = :max_sms_end_date,
            out_date = :max_out_date
        where
            id=:id;
    """)

    delete_pack_sql = text("""
        delete from pack where uid=:uid and id<>:id;
    """)

    have_duplicate_pack_users = conn.execute(
        find_duplicate_pack_users_sql
    ).fetchall()

    for user in have_duplicate_pack_users:
        user_id = user.uid

        # 判断该保留的套餐记录
        pack_records = conn.execute(
            find_pack_with_uid_sql,
            uid=user_id
        ).fetchall()

        valid_pack = None
        for i in pack_records:
            if i.deleted_at == 0:
                valid_pack = i

        # 更新其他记录的字段最大值到 valid_pack
        if valid_pack:
            max_call_point = max([i.call_point for i in pack_records])
            max_bid_point = max([i.bid_point for i in pack_records])
            max_sms_point = max([i.sms_point for i in pack_records])
            max_dispatch_point = max([i.dispatch_point for i in pack_records])
            max_paid_sms_point = max([i.paid_sms_point for i in pack_records])
            max_accum_call_point = max([i.accum_call_point for i in pack_records])
            max_accum_bid_point = max([i.accum_bid_point for i in pack_records])
            max_accum_sms_point = max([i.accum_sms_point for i in pack_records])
            max_accum_dispatch_point = max([i.accum_dispatch_point for i in pack_records])
            max_end_date = max([i.end_date for i in pack_records])

            max_sms_end_date = None
            max_out_date = None
            for i in pack_records:
                if i.sms_end_date:
                    max_sms_end_date = i.sms_end_date
                if i.out_date:
                    max_out_date = i.out_date

            print(user_id, max_call_point, max_bid_point, max_sms_point, max_dispatch_point, max_paid_sms_point, max_accum_call_point, max_accum_bid_point, max_accum_sms_point, max_accum_dispatch_point, max_end_date, max_sms_end_date, max_out_date)
            conn.execute(
                update_valid_pack_sql,
                max_call_point=max_call_point,
                max_bid_point=max_bid_point,
                max_sms_point=max_sms_point,
                max_dispatch_point=max_dispatch_point,
                max_paid_sms_point=max_paid_sms_point,
                max_accum_call_point=max_accum_call_point,
                max_accum_bid_point=max_accum_bid_point,
                max_accum_sms_point=max_accum_sms_point,
                max_accum_dispatch_point=max_accum_dispatch_point,
                max_end_date=max_end_date,
                max_sms_end_date=max_sms_end_date,
                max_out_date=max_out_date,
                id=valid_pack.id
            )

        # 删除其他记录
        valid_pack_id = valid_pack.id if valid_pack is not None else 0
        conn.execute(
            delete_pack_sql,
            uid=user_id,
            id=valid_pack_id,
        )

if __name__ == '__main__':
    clear_duplicate_pack()
